﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
public class DBHelp
{
    SqlConnection con;
    SqlCommand com;
    SqlDataAdapter adapter;
    string chuoi = "myconn";
	public DBHelp()
	{

        if (con == null || con.State == ConnectionState.Closed)
        {
            Connections();
        }
	}

    private void Connections()
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings[chuoi].ConnectionString);
        con.Open();
    }
    public void CloseConnection()
    {
        if (con.State == ConnectionState.Open)
            con.Close();
    }
    public void Pre(SqlCommand com, string SPName, List<SqlParameter> listparam)
    {
        if (con == null || con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        com.Connection = con;
        com.CommandText = SPName;
        com.CommandType = CommandType.StoredProcedure;
        com.CommandTimeout = 500;
        if (listparam != null)
        {
            foreach (SqlParameter item in listparam)
            {
                com.Parameters.Add(item);
            }
        }
    }
    public SqlDataAdapter DataAdapters(string SPName, List<SqlParameter> listparam)
    {
        com = new SqlCommand();
        Pre(com, SPName, listparam);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = com;
        CloseConnection();
        return da;
    }
    public SqlDataReader DataReaders(string SPName, List<SqlParameter> listparam)
    {
        com = new SqlCommand();
        Pre(com, SPName, listparam);
        return com.ExecuteReader();
    }
    //get all
    public DataSet GetAll(string SPName,List<SqlParameter> paramList)
    {
        com = new SqlCommand();
        adapter = new SqlDataAdapter();
        DataSet dtSet = new DataSet();
        Pre(com, SPName, paramList);
        adapter.SelectCommand = com;
        adapter.Fill(dtSet);
        CloseConnection();
        return dtSet;
    }
    //execute query
    public int Execute_Querry(string SPName,List<SqlParameter> paramList)
    {
        com = new SqlCommand();
        Pre(com, SPName, paramList);
        int i = com.ExecuteNonQuery();
        CloseConnection();
        return i;
    }
}
